package jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;


import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;


/**
 * 德鲁伊数据库连接池技术+预编译+封装解耦
 */

public class MyHandleDB {
//    整个过程就是，connection是一个全局变量，在创建对象时首先自动执行无参构造，然后他就在各个方法里有值了
    public Connection connection;

    /**
     * 无参构造，创建对象时自执行
     */
    public MyHandleDB() throws Exception {
        this.conn();
    }

    public static void main(String[] args) throws Exception {
        MyHandleDB myHandleDB = new MyHandleDB();
        ArrayList<Student> students = myHandleDB.selectDate("select * from students");
    }

    /**
     * 通过德鲁伊连接池获取数据库连接
     * 实现逻辑：
     * 1. 读取配置文件（jdbc.properties）获取数据库连接参数
     * 2. 使用德鲁伊连接池工厂创建数据源
     * 3. 从数据源获取数据库连接并返回
     */
    private void conn() throws Exception {
        Properties properties = new Properties();
        FileInputStream fileInputStream = new FileInputStream("src/jdbc3/jdbc.properties");
        properties.load(fileInputStream);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
    }

    /**
     * 用于释放资源的方法
     * @param connection
     * @param preparedStatement
     */
    public void release(Connection connection,PreparedStatement preparedStatement){
        try {
            if (connection!=null){
                connection.close();
            }
            if (preparedStatement!=null){
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 1、查询数据
     * @return
     * @throws Exception
     */
    public ArrayList<Student> selectDate(String sql) throws Exception {
        ArrayList<Student> students = new ArrayList<>();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //创建一个预编译对象，预编译对象时已经编译sql，再使用预编译对象执行
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            Student student = new Student();
            student.setSno(resultSet.getString("sno"));
            student.setSname(resultSet.getString("sname"));
            student.setSsex(resultSet.getString("ssex"));
            student.setSbirthday(resultSet.getDate("sbirthday"));
            student.setClassname(resultSet.getString("classname"));
            students.add(student);
        }
        release(connection,preparedStatement);
        return students;
    }

    /**
     * 2、插入数据
     * @throws Exception
     */

    public void insertDate() {
        try {
            String sql = "insert into students values(?,?,?,?,?)";
            String sno="102";
            String sname="翠花";
            String ssex="男";
            //直接写2004-06-01这种形式，Java 编译器会认为这是一个算术表达式而不是一个能直接赋值给 Date 类型变量的值
            Date sbirthday=Date.valueOf("2004-06-01");
            String classname="95033";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sno);
            preparedStatement.setString(2,sname);
            preparedStatement.setString(3,ssex);
            preparedStatement.setDate(4,sbirthday);
            preparedStatement.setString(5,classname);
            int i = preparedStatement.executeUpdate();
            System.out.println("成功插入"+i+"条信息");
            release(connection,preparedStatement);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 3、更新数据
     */
    public void updateDate() {
        try {
            String sql = "update students set sname=? where sno=?";
            String sno="102";
            String sname="光头强";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sname);
            preparedStatement.setString(2,sno);
            int i = preparedStatement.executeUpdate();
            System.out.println("成功修改"+i+"条信息");
            release(connection,preparedStatement);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 4、删除数据
     */
    public void deleteDate(){
        try {
            String sql = "delete from students where sno=?";
            String sno="102";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sno);
            int i = preparedStatement.executeUpdate();
            System.out.println("成功删除"+i+"条信息");
            release(connection,preparedStatement);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
